package persistencia;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import model.Plan;
import model.PlanItem;
import model.Service;



public class PlanMapper {

	private static PlanMapper s_planMapper;

	private PlanMapper() {
	}

	public static PlanMapper GetInstancia() {
		if (s_planMapper == null) {
			s_planMapper = new PlanMapper();
		}

		return s_planMapper;
	}

	
	public void InsertPlan(Plan p)
	{
		Connection con = PoolConnection.getPoolConnection().getConnection();

		try {
			String senten = "INSERT INTO [Plan](isAdditionalPlan, isPrepaidPlan, cost, discount,planDescription) VALUES (?,?,?,?,?)";
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			//ps.setInt(1,Integer.parseInt(p.getPlanNumber()));
			ps.setBoolean(1, p.getIsAdditionalPlan());
			ps.setBoolean(2, p.getIsPrepaidPlan());
			ps.setFloat(3, p.getPlanCost());
			ps.setFloat(4, p.getDiscount());
			ps.setString(5, p.getPlanDescription());
			ps.executeUpdate();	
			
			senten = "select planNumber from  [Plan] where planNumber >= all (select " +
					"planNumber from [plan])";
			ps = con.prepareStatement(senten);
			ResultSet result = ps.executeQuery();
			while (result.next()){
				p.setPlanNumber(result.getInt("planNumber"));
			}
		} catch (SQLException e) {
			System.out.println("Mensaje Error during Plan Insertion: "
					+ e.getMessage());
			System.out.println("Stack Trace for  Plan Insertion Error: "
					+ e.getStackTrace());
			
		}
		finally
		{
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}
		
		for (PlanItem pi : p.getPlanItems()) {
			
			AddPlanItem(pi,p.getPlanNumber());
		}
		
	}
	
	public void UpdatePlan(Plan p)
	{
		
		Connection con = PoolConnection.getPoolConnection().getConnection();

		try {
			String senten = "update [Plan] " +
					" SET isAdditionalPlan = ?, isPrepaidPlan=?, cost=?, discount=? where planNumber =?";
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			ps.setBoolean(1, p.getIsAdditionalPlan());
			ps.setBoolean(2, p.getIsPrepaidPlan());
			ps.setFloat(3, p.getPlanCost());
			ps.setFloat(4, p.getDiscount());
			ps.setInt(5, p.getPlanNumber());
			ps.executeUpdate();
			PoolConnection.getPoolConnection().realeaseConnection(con);
		} catch (SQLException e) {
			System.out.println("Message Error during Plan update: "
					+ e.getMessage());
			System.out.println("Stack Trace Error for  Plan update:  "
					+ e.getStackTrace());
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}
	}
	
	public void AddPlanItem(PlanItem pi, int planNumber)
	{
		Connection con = PoolConnection.getPoolConnection().getConnection();

		try {

		String senten = "INSERT INTO PlanItem (PlanNumber, ServiceName, ServiceCost, ServiceFreeUnits) VALUES (?,?,?,?)";
		PreparedStatement ps = null;
		ps = con.prepareStatement(senten);
		ps.setInt(1,planNumber);
		ps.setString(2, pi.getServiceName());
		ps.setFloat(3, pi.getUnitServiceCostInPLan());
		ps.setInt(4, pi.getServiceFreeUnits());
		ps.executeUpdate();
		PoolConnection.getPoolConnection().realeaseConnection(con);
		} catch (SQLException e) {
			System.out.println("Message Error during Plan Item Insertion: "
					+ e.getMessage());
			System.out.println("Stack Trace Error for  Plan IOtem Insertion Error:  "
					+ e.getStackTrace());
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}
	}
	
	public ArrayList<Plan> GetAllPlans()
	{
		Connection con = PoolConnection.getPoolConnection().getConnection();
		ArrayList<Plan> ret = new ArrayList<Plan>();
		Plan plan = null;
		try {
			String senten = "select * from [Plan]";
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			
			ResultSet result = ps.executeQuery();
			while (result.next())
			{
				plan = new Plan();
				
				plan.populatePlan(result.getInt("planNumber"),result.getFloat("cost"),
						result.getFloat("discount"), result.getBoolean("isAdditionalplan"), 
						result.getBoolean("isPrepaidPlan"), result.getString("planDescription"));
				//plan.setPlanNumber(result.getString("planNumber"));
				//plan.setIsPrepaidPlan(result.getBoolean("isPrepaidPlan"));
				//plan.setIsAdditionalPlan(result.getBoolean("isAdditionalplan"));
				//plan.setDiscount(result.getFloat("discount"));
				//plan.setPlanCost(result.getFloat("cost"));				
				ret.add(plan);
			}
			PoolConnection.getPoolConnection().realeaseConnection(con);	
		} catch (SQLException e) {
			System.out.println("Message Error during Plan load: "
					+ e.getMessage());
			System.out.println("Stack Trace Error for  Plan load Error:  "
					+ e.getStackTrace());
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}
		return ret;
	}
	
	public ArrayList<PlanItem> LoadPlanItems(int planNumber,ArrayList<Service> servicios)
	{
		ArrayList<PlanItem> ret = new ArrayList<PlanItem>();
		Connection con = PoolConnection.getPoolConnection().getConnection();
		PlanItem planItem = null;
		try {
			String senten = "select ServiceName, serviceCost, serviceFreeUnits from PlanItem where PlanNumber =?";
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			ps.setInt(1,planNumber);			
			ResultSet result = ps.executeQuery();
			while (result.next())
			{
				String servicioId = result.getString("ServiceName");
				for (Service s : servicios) {
					if (s.getServiceName().equalsIgnoreCase(servicioId))
					{
						planItem = new PlanItem(s, result.getFloat("serviceCost"), result.getInt("serviceFreeUnits"));
						ret.add(planItem);
						continue;
					}
				}
			}

			PoolConnection.getPoolConnection().realeaseConnection(con);
		} catch (SQLException e) {
			System.out.println("Message Error during Plan Items  load: "
					+ e.getMessage());
			System.out.println("Stack Trace Error for  Plan Items load Error:  "
					+ e.getStackTrace());
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}
		return ret;
	}

	public Plan GetPlan(int  planNumber) {
		Connection con = PoolConnection.getPoolConnection().getConnection();
		Plan plan = null;
		try {
			String senten = "select * from [Plan] where planNumber =?";
			PreparedStatement ps = null;
			ps = con.prepareStatement(senten);
			
			ResultSet result = ps.executeQuery();
			while (result.next())
			{
				plan = new Plan();
				
				plan.populatePlan(result.getInt("planNumber"),result.getFloat("cost"),
						result.getFloat("discount"), result.getBoolean("isAdditionalplan"), 
						result.getBoolean("isPrepaidPlan"),result.getString("planDescription"));	
				plan.populatePlanItems(PlanMapper.GetInstancia().LoadPlanItems(plan.getPlanNumber(), 
						ServiceMapper.GetInstancia().LoadAllServices()));
			}
			PoolConnection.getPoolConnection().realeaseConnection(con);	
		} catch (SQLException e) {
			System.out.println("Message Error during Plan load: "
					+ e.getMessage());
			System.out.println("Stack Trace Error for  Plan load Error:  "
					+ e.getStackTrace());
			PoolConnection.getPoolConnection().realeaseConnection(con);
		}
		return plan;
	}
	
	
	
}
